import json
from control.models import *
from unit_management.models import *
import pandas as pd
from django.db import IntegrityError, transaction
import glob


def init_unit_dataset():
    with transaction.atomic():
        super_admin_entity = User.objects.filter(
            user_account="superadmin").first()
        matched_files = glob.glob(
            "../final_basic_tables/A080A. PCdata基表-8-量表*.xlsx")
        latest_file = max(matched_files, key=lambda x: x.split(
            "-")[-1]) if matched_files else None
        sheet_name = "单位制（入库版）"
        df = pd.read_excel(latest_file, sheet_name=sheet_name,
                           skiprows=[0], dtype=str)
        df = df.iloc[:, :8]
        df.columns = ["quantity_sei_id", "quantity_abbreviation",
                      "quantity_type", "unit_group_id", "unit_cn_id", "unit_cn_symbol",
                      "unit_en_id", "unit_en_symbo"]

        unitset_item = {}
        unitset_item["unit_set_comment"] = "系统初始化"
        unitset_item["unit_set_description"] = "国际单位制（Système International d'Unités，简称 SI）是目前全球科学、工程和日常生活中广泛使用的标准单位制，具有统一性和精确性。"
        unitset_item["unit_set_description_en"] = "The International System of Units (Système International d'Unités, abbreviated as SI) is the standard system of units widely used in global science, engineering, and daily life, renowned for its uniformity and precision."
        unitset_item["create_person"] = super_admin_entity
        unitset_item["modify_person"] = super_admin_entity
        unitset_item["is_system"] = True
        unitset_item["valid_flag"] = True
        unitset_item["unit_set_name"] = f"标准单位制"
        unitset_item["unit_set_name_en"] = f"Standard Unit Set"
        unitset_cn = UnitSet.objects.create(**unitset_item)

        unitset_item = {}
        unitset_item["unit_set_comment"] = "系统初始化"
        unitset_item["unit_set_description"] = "英制单位制起源于中世纪的英国，基于传统度量衡（如人体部位、日常工具等），后随英国殖民扩张传播至北美、印度等地区。"
        unitset_item[
            "unit_set_description_en"] = "The Imperial system of units originated in medieval Britain, based on traditional weights and measures (such as body parts and everyday tools), which spread to regions like North America and India through British colonial expansion."
        unitset_item["create_person"] = super_admin_entity
        unitset_item["modify_person"] = super_admin_entity
        unitset_item["is_system"] = True
        unitset_item["valid_flag"] = True
        unitset_item["unit_set_name"] = f"英制单位制"
        unitset_item["unit_set_name_en"] = f"Imperial Unit Set"
        unitset_en = UnitSet.objects.create(**unitset_item)
        for index, row in df.iterrows():

            basic_unit = Unit.objects.filter(sei_id=row["unit_cn_id"]).first()
            quantity = Quantity.objects.filter(
                quantity_abbreviation=row["quantity_abbreviation"]).first()
            if quantity == None:
                print(row["quantity_abbreviation"])
            UnitSetContent.objects.create(
                unit_set=unitset_cn, quantity=quantity, unit=basic_unit, valid_flag=True)

            basic_unit = Unit.objects.filter(sei_id=row["unit_en_id"]).first()
            quantity = Quantity.objects.filter(
                quantity_abbreviation=row["quantity_abbreviation"]).first()
            if quantity == None:
                print(row["quantity_abbreviation"])
            UnitSetContent.objects.create(
                unit_set=unitset_en, quantity=quantity, unit=basic_unit, valid_flag=True)


def fix_unit_quantity_map():
    # 修复文件路径和文件名匹配问题
    quantity_file = glob.glob("../final_basic_tables/A080A. PCdata基表-8-量表(单位集)-*.xlsx")
    quantity_file = max(quantity_file, key=lambda x: x.split("-")[-1]) if quantity_file else None
    if not quantity_file:
        raise FileNotFoundError("未找到量表文件")
    sheet_name = "量表（入库版）"
    df_quantity = pd.read_excel(quantity_file, sheet_name=sheet_name, skiprows=[0, 2], dtype=str).fillna("")

    # 修复单位表文件路径和文件名匹配问题
    unit_files = glob.glob("../final_basic_tables/A073. PCdata基表-1-单位表*.xlsx")
    unit_file = max(unit_files, key=lambda x: x.split("-")[-1]) if unit_files else None
    if not unit_file:
        raise FileNotFoundError("未找到单位表文件")
    
    sheet_name = "单位表（入库版）"
    df_unit = pd.read_excel(unit_file, sheet_name=sheet_name, skiprows=[0, 2], dtype=str).fillna("")

    unit_dict = {}
    for index, row in df_unit.iterrows():
        if row["sPCdataUnitGroupID"] != "":
            group_id = row["sPCdataUnitGroupID"]
            if group_id not in unit_dict:
                unit_dict[group_id] = []
            unit_dict[group_id].append(row["nPCdataUnitID"])

    for index, row in df_quantity.iterrows():
        if row["nPCdataQuantityID"] != "":
            quantity_entity = Quantity.objects.filter(
                sei_id=row["nPCdataQuantityID"]).first()
            if quantity_entity is None:
                print("没有量：", row["nPCdataQuantityID"])
                continue
            group_id = row["sPCdataUnitGroupID"]
            if group_id not in unit_dict or not unit_dict[group_id]:
                print(f"警告: 单位组 {group_id} 不存在或没有关联的单位")
                continue
            for unit_id in unit_dict[group_id]:
                unit_entity = Unit.objects.filter(sei_id=unit_id).first()
                if unit_entity is None:
                    print("没有单位：", unit_id)
                    continue
                print(f"create {unit_id} - {group_id}")
                QuantityUnitMap.objects.get_or_create(
                    quantity=quantity_entity,
                    unit=unit_entity
                )
